import pandas as pd

# 配置常量
FILE_PATH = 'd:/git-mcp-test/project/练习一/FhjlViewDD.xlsx'
DATE_COLUMN = '创建时间'
LICENSE_PLATE_COLUMN = '车辆'  # 假设车牌号列名为 '车辆'
FREIGHT_COLUMN = '净重'  # 假设货运量列名为 '净重'
OUTPUT_FILE = 'd:/git-mcp-test/project/练习一/june_license_plate_freight.csv'
VISUALIZATION_FILE = 'd:/git-mcp-test/project/练习一/june_license_plate_freight.html'

# 配置 matplotlib 支持中文显示
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False

import plotly.express as px

# 封装数据处理函数
if __name__ == '__main__':
    try:
        # 读取 Excel 文件
        df = pd.read_excel(FILE_PATH)
        
        # 转换日期列
        df[DATE_COLUMN] = pd.to_datetime(df[DATE_COLUMN])
        
        # 筛选 6 月份的数据
        june_data = df[df[DATE_COLUMN].dt.month == 6]
        
        # 按车牌号分组并计算总货运量
        total_freight = june_data.groupby(LICENSE_PLATE_COLUMN)[FREIGHT_COLUMN].sum()
        
        # 按总货运量降序排序
        sorted_freight = total_freight.sort_values(ascending=False)
        sorted_freight = sorted_freight.reset_index()
        
        # 保存结果到 CSV 文件
        try:
            sorted_freight.to_csv(OUTPUT_FILE)
        except PermissionError:
            print(f'无法写入文件 {OUTPUT_FILE}，请确保文件未被其他程序占用后重试。')
            raise

        # 使用 Plotly 创建交互式图表
        fig = px.bar(
            sorted_freight,
            x=LICENSE_PLATE_COLUMN,
            y=FREIGHT_COLUMN,
            title='6 月份各车辆的总货运量',
            labels={
                LICENSE_PLATE_COLUMN: '车辆',
                FREIGHT_COLUMN: '总货运量'
            },
            template='plotly_white',
            color=FREIGHT_COLUMN,
            color_continuous_scale='Viridis',
            hover_data=[FREIGHT_COLUMN]
        )
        fig.update_layout(
            xaxis_tickangle=45,
            title_font_size=24,
            title_x=0.5,
            xaxis_title_font_size=18,
            yaxis_title_font_size=18,
            font_family='Microsoft YaHei',
            uniformtext_minsize=12,
            uniformtext_mode='hide',
            plot_bgcolor='rgba(0,0,0,0)',
            paper_bgcolor='rgba(0,0,0,0)',
            coloraxis_colorbar_title='总货运量'
        )
        fig.update_traces(
            marker_line_width=1,
            marker_line_color='black'
        )
        
        try:
            fig.write_html(VISUALIZATION_FILE)
        except PermissionError:
            print(f'无法保存文件 {VISUALIZATION_FILE}，请确保路径可写且未被其他程序占用后重试。')
            raise
        
        print(f'6 月份各车辆的总货运量统计完成，结果已保存至 {OUTPUT_FILE}')
        print(f'6 月份各车辆的总货运量可视化完成，结果已保存至 {VISUALIZATION_FILE}')
    except Exception as e:
        print(f'处理过程中出现错误: {e}')